package utils;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import com.shove.Convert;
import models.CustomerInfo;
import models.files;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import play.Play;
import play.db.jpa.Blob;
public class ExcelUtils {
    /**
     * 根据输入流生成实体集合
     *
     * @param is
     * @return
     * @throws IOException
     * @author Changhai
     * @data 2017-7-5
     */
    public static List<CustomerInfo> getList(InputStream is,boolean isDealImage,List<String> imgFileName)throws IOException {

        List<List<String>> list = ExcelUtils.readExcel(is,isDealImage,imgFileName);

        //-----------------------遍历数据到实体集合开始-----------------------------------
        List<CustomerInfo> listBean = new ArrayList<>();
        for (int z=0,i = 1; i < list.size(); i++,z++) {
            // i=1是因为第一行不要
            CustomerInfo uBean = new CustomerInfo();
            List<String> listStr = list.get(i);
            for (int j = 0; j < listStr.size(); j++) {
                switch (j) {
                    case 0:
                        uBean.客户姓名=listStr.get(j);
                        break;// 第一列
                    case 1:
                        if(isDealImage){
                            uBean.烫画图片=imgFileName.get(z);
                        }else{
                            uBean.烫画图片 = "";
                        }
                        break;// 第二列
                    case 2:
                        String dateStr = listStr.get(j);
                        dateStr = dateStr.replaceAll("/","-");
                        Date date = DateUtil.strToYYMMDDDate(dateStr);
                        uBean.下单时间 = date;
                        break;
                    case 3:
                        uBean.价格 = Convert.strToDouble(listStr.get(j),0);
                        break;
                    case 4:
                        uBean.张数 = Convert.strToInt(listStr.get(j),0);
                        break;
                    case  5:
                        uBean.金额 = Convert.strToDouble(listStr.get(j),0);
                        break;
                    case 6:
                        uBean.付款状态 = listStr.get(j);
                        break;
                    case 7:
                        uBean.客户级别 = listStr.get(j);
                        break;
                    case  8:
                        uBean.客户来源 = listStr.get(j);
                        break;
                }
            }
            listBean.add(uBean);
        }
        //----------------------------遍历数据到实体集合结束----------------------------------
        return listBean;
    }
    /**
     * Excel读取 操作
     */
    public static List<List<String>> readExcel(InputStream is,boolean isDealImage,List<String> imgFileNameList)throws IOException {
        HSSFWorkbook wb = null;
        try {
            wb = (HSSFWorkbook) WorkbookFactory.create(is);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        /** 得到第一个sheet */
        HSSFSheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<List<String>> dataLst = new ArrayList<>();
        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell){
                    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    cellValue = hSSFDataFormatter.formatCellValue(cell);
                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        // 如果存在图片类型单独处理
        if(isDealImage){
            List<HSSFPictureData> pictures = wb.getAllPictures();
            int i = 0;
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int row = anchor.getRow1();
                    int pictureIndex = pic.getPictureIndex()-1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    try {
                        String filename = savePic(row, picData);
                        imgFileNameList.add(filename);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                i++;
            }
        }
        return dataLst;
    }
    private static String savePic(int i, PictureData pic) throws Exception {
        String abpath = Constants.getAbsPath();
        String filename = UUID.randomUUID().toString();
        String absolutePath = abpath + Constants.abspath + filename;
        String ext = pic.suggestFileExtension();
        byte[] data = pic.getData();
        FileOutputStream out = new FileOutputStream(absolutePath);
        out.write(data);
        out.close();

        files f = new files();
        f.file_realname = "excel_nei_file";
        f.filename = filename;
        f.filetype = ext;
        f.size = data.length/1024;
        f.create_date = new Date();
        f.save();
        return filename;
    }
    public static void main(String[] args) {
        try {
            //根据流
            InputStream is = new FileInputStream("D:\\git-project\\play-tuqiangtanghua\\public\\模版1.xls");
            List<String> filenameList = new ArrayList<>();
            List<CustomerInfo> list = ExcelUtils.getList(is,false,filenameList);
            //根据文件路径
            for (int i = 0; i < list.size(); i++) {
                CustomerInfo cBean = list.get(i);
                System.out.println(cBean.烫画图片);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}